package com.punai.common.utils.sqlite;


import cn.hutool.core.bean.BeanUtil;
import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.convert.Convert;
import cn.hutool.core.text.StrBuilder;
import cn.hutool.core.util.StrUtil;
import com.punai.common.exception.ServiceException;
import io.netty.util.internal.StringUtil;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Configuration;

import javax.annotation.PostConstruct;
import javax.annotation.PreDestroy;
import java.lang.reflect.Field;
import java.lang.reflect.Modifier;
import java.math.BigDecimal;
import java.sql.*;
import java.util.Date;
import java.util.*;

@Configuration
@ConfigurationProperties(prefix = "sqlite")
public class SqliteUtils {
	private static final Logger log = LoggerFactory.getLogger(SqliteUtils.class);
	// 单链接对象
	private Connection connection = null;
/*	private PreparedStatement pstat = null;
	private ResultSet rst = null;*/
	private static String driver="org.sqlite.JDBC";
	private String dataSource;
	private String userTempl;
	private String unit;
    static{
        try {
            Class.forName(driver);
        } catch (ClassNotFoundException e) {
           e.printStackTrace();
       }
	}   
    
    @PostConstruct
    public void initSqliteConnection() throws ServiceException {
		if(StringUtil.isNullOrEmpty(dataSource)) {
			throw new ServiceException("sqlite.message.patherror");
		}
		this.connection = getConnection(dataSource);
	}
    
    
	public Connection getConnection(String path) {
		try {
			connection = DriverManager.getConnection("jdbc:sqlite:"+path);
		} catch (Exception e) {
			System.err.println("[SQL LITE]: 获取sqlite的jdbc连接失败");
			System.err.println(e.getMessage());
		}
		return connection;
	}
	
	/**
	 *   执行修改删除插入操作( 同步方法)
	 * @param sql
	 * @param args
	 * @return 
	 */
	public synchronized int execute(String sql, Object ...args) {
		int executeUpdate = 0;
		PreparedStatement pstat = null;
		ResultSet rst = null;
		try {
			System.out.println("[SQL EXECUTE]: " + sql);
			pstat = connection.prepareStatement(sql);
			if(args.length>0) {
				for (int i = 0, len = args.length; i < len; i++) {
					pstat.setObject(i+1, args[i]);
				}
			}
			
			executeUpdate = pstat.executeUpdate();
			rst = pstat.getGeneratedKeys();
			
			if(rst!=null) {
				
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			close(pstat,rst);
		}
		return executeUpdate;
	}
	
	/**
	 * 查询一条记录
	 * @param sql
	 * @return
	 */
	public Map unique(String sql, Object ...args) {
		Map result = null;
		PreparedStatement pstat = null;
		ResultSet rst = null;
		try {
			System.out.println("[SQL UNIQUE]: " + sql);
			pstat = connection.prepareStatement(sql);
			if(args.length>0) {
				for (int i = 0, len = args.length; i < len; i++) {
					pstat.setObject(i+1, args[i]);
				}
			}
			rst = pstat.executeQuery();
			
			//获取到
			ResultSetMetaData metaData = rst.getMetaData();
			int cols = metaData.getColumnCount();
			
			if(rst.next()) {
				//封装一行数据
				result = new HashMap();
				for (int i = 0; i < cols; i++) {
					String key = metaData.getColumnName(i+1);
					Object value = rst.getObject(i+1);
					result.put(key, value);
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			close(pstat,rst);
		}
		return result;
	}
	
	/**
	 * 查询一个列表中的数据
	 * @param sql
	 * @return
	 */
	public List<Map<String,Object>> list(String sql) {
		List results = new ArrayList();
		PreparedStatement pstat = null;
		ResultSet rst = null;
		try {
			System.out.println("[SQL LIST]: " + sql);
			pstat = connection.prepareStatement(sql);
			rst = pstat.executeQuery();
			
			//获取到
			ResultSetMetaData metaData = rst.getMetaData();
			int cols = metaData.getColumnCount();
			
			while(rst.next()) {
				//封装一行数据
				Map map = new HashMap();
				for (int i = 0; i < cols; i++) {
					String key = metaData.getColumnName(i+1);
					Object value = rst.getObject(i+1);
					map.put(key, value);
				}
				results.add(map);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			close(pstat,rst);
		}
		return results;
	}
	
	public int count(String tableName) {
		return count(tableName, null);
	}
	
	/**
	 * 查看统计计数
	 * @param tableName
	 * @param where
	 * @return
	 */
	public int count(String tableName, String where) {
		int count = 0;
		PreparedStatement pstat = null;
		ResultSet rst = null;
		try {
			String sql = "select count(*) from "+tableName+" "+(where==null?"":where);
			System.out.println("[SQL Count]: "+sql);
			pstat = connection.prepareStatement("select count(*) from "+tableName+" "+(where==null?"":where));
			rst = pstat.executeQuery();
			
			if(rst.next()) count = rst.getInt(1);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			close(pstat,rst);
		}
		return count;
	}
	
	/**
	 * 清除单次查询的连接
	 */
	private void close(PreparedStatement pstat,ResultSet rst) {
		if(rst!=null) {
			try {
				rst.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		if(pstat!=null) {
			try {
				pstat.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
	
	/**
	 * spring 容器关闭时先关闭sqlLite 链接
	 */
	@PreDestroy
	public void relase() {
		if(connection!=null) {
			try {
				connection.close();
				System.out.println("[SQL LITE]: 关闭connection连接");
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
	
	private <T> T mapToBean(Map<String, Object> map,T bean,Map<String, String> entityDbFieldMap) throws IllegalArgumentException, IllegalAccessException {
		Class<?> clazz = bean.getClass();



		for(;clazz != Object.class; clazz = clazz.getSuperclass()) {
			Field[] declaredFields = clazz.getDeclaredFields();
			for (Field field : declaredFields) {
				Class<?> type = field.getType();
				// 访问私有变量
				field.setAccessible(true);
				int modifiers = field.getModifiers();
				// 排除掉静态变量
				if(Modifier.isStatic(modifiers) && Modifier.isFinal(modifiers)) {
					continue;
				}
				Object val = map.get(field.getName());
				if (val == null) {
					String dbColumnName = entityDbFieldMap.get(field.getName());
					val = map.get(dbColumnName);
				}
				// 做对应类型转换
				if (type.equals(BigDecimal.class) || type.equals(double.class) || type.equals(float.class)) {
					field.set(bean, val == null ? null : StrUtil.isNotEmpty(val.toString()) ? new BigDecimal(val.toString()) : null);
				}else if(type.equals(List.class)){
					// 防止设置为null
				}else if(type.equals(Integer.class) ){
					field.set(bean,Integer.parseInt(val==null ? "0" : val.toString()));
				}else if (type.equals(Date.class)) {
					try {
						field.set(bean,(Date)val);
					}catch(Exception e) {
						field.set(bean, Convert.toDate(val));
					}
				}else if (type.equals(Boolean.class)) {
					Boolean flag = true;
					if (val == null || !val.equals(1)) {
						flag = false;
					}
					field.set(bean, flag);
				}else if (type.equals(String.class)) {
					String tempVal = "";
					try {
						tempVal = (String)val;
					}catch (Exception e) {
						tempVal = val + "";
					}
					
					field.set(bean,tempVal);
				}
			}
		}
		return bean;
	}


	/**
	 * 务必开启事务
	 * @param list
	 * @param clazz
	 * @return
	 */
	public <T> int insert(List<T> list,Class<T> clazz) {
		if (CollUtil.isEmpty(list)) {
			return 0;
		}
		if (!clazz.isAnnotationPresent(SLTDesc.class)) {
			log.error("不支持的插入类型!");
			return 0;
		}

		SLTDesc sltDesc = clazz.getAnnotation(SLTDesc.class);
		String tName = sltDesc.tName();
		String name = StrUtil.isEmpty(tName) ? clazz.getSimpleName() : tName;
		StrBuilder sb = new StrBuilder();
		sb.append("INSERT INTO ").append(name).append("(");
		ComboFieldData fieldData = getFieldStrList(clazz);
		List<String> columnNames = fieldData.getColumnNames();
		Map<String, String> defValueMap = fieldData.getDefValueMap();
		Map<String,String> entityFieldDbMap = fieldData.getEntityFieldDbMap();
		String whyStr = "";
		for (int i = 0; i < columnNames.size(); i++) {
			String columnName = columnNames.get(i);
			if (i < columnNames.size() - 1) {
				whyStr += "?,";
				sb.append(columnName).append(",");
			} else  {
				whyStr += "?";
				sb.append(columnName);
			}
		}
		sb.append(") VALUES(");
		sb.append(whyStr).append(")");

		PreparedStatement pstat = null;
		ResultSet rst = null;
		String sql = sb.toString();
		log.info("插入sql: {}",sql);
		try {
			pstat = connection.prepareStatement(sql);
			int addSize = 0;
			for (Object sqliteEntity : list) {
				addSize++;
				Map<String, Object> objMap = BeanUtil.beanToMap(sqliteEntity);
				for (int i = 0; i < columnNames.size(); i++) {
					String columnName = columnNames.get(i);
					Object o = objMap.get(entityFieldDbMap.get(columnName));
					if (o == null) {
						o = defValueMap.get(columnName);
					}
					pstat.setObject(i+1,o);
				}
				pstat.addBatch();
				if(addSize%2000==0){
					pstat.executeBatch();
					pstat.clearBatch();
					addSize = 0;
				}
			}
			if (addSize != 0) {
				// 最后再执行一次
				pstat.executeBatch();
			}
		} catch (SQLException throwables) {
			throwables.printStackTrace();
		} finally {
			close(pstat,rst);
		}
		return 1;
	}

	private ComboFieldData getFieldStrList(Class<?> clazz) {

		ComboFieldData comboFieldData = new ComboFieldData();

		List<String> columnNames = new ArrayList<>();
		Map<String,String> defValueMap = new HashMap<>();
		Map<String,String> entityFieldDbMap = new HashMap<>();
		Map<String,String> entityDbFieldMap = new HashMap<>();

		Field[] declaredFields = clazz.getDeclaredFields();
		for (Field declaredField : declaredFields) {
			String name = declaredField.getName();
			if (declaredField.isAnnotationPresent(SLCDesc.class)) {
				SLCDesc annotation = declaredField.getAnnotation(SLCDesc.class);
				String tc = annotation.tc();
				if (StrUtil.isEmpty(tc)) {
					tc = name;
				}
				columnNames.add(tc);
				String s = annotation.defValue();
				defValueMap.put(tc,s);
				entityFieldDbMap.put(tc,name);
				entityDbFieldMap.put(name,tc);
			}else{
				entityDbFieldMap.put(name, StrUtil.toUnderlineCase(name).toUpperCase());
			}
		}
		comboFieldData.setColumnNames(columnNames);
		comboFieldData.setDefValueMap(defValueMap);
		comboFieldData.setEntityFieldDbMap(entityFieldDbMap);
		comboFieldData.setEntityDbFieldMap(entityDbFieldMap);
		return comboFieldData;
	}

	class ComboFieldData{
		private List<String> columnNames = new ArrayList<>();
		private Map<String,String> defValueMap = new HashMap<>();
		private Map<String,String> entityFieldDbMap = new HashMap<>();
		private Map<String,String> entityDbFieldMap = new HashMap<>();

		public List<String> getColumnNames() {
			return columnNames;
		}

		public void setColumnNames(List<String> columnNames) {
			this.columnNames = columnNames;
		}

		public Map<String, String> getDefValueMap() {
			return defValueMap;
		}

		public void setDefValueMap(Map<String, String> defValueMap) {
			this.defValueMap = defValueMap;
		}

		public Map<String, String> getEntityFieldDbMap() {
			return entityFieldDbMap;
		}

		public void setEntityFieldDbMap(Map<String, String> entityFieldDbMap) {
			this.entityFieldDbMap = entityFieldDbMap;
		}

		public Map<String, String> getEntityDbFieldMap() {
			return entityDbFieldMap;
		}

		public void setEntityDbFieldMap(Map<String, String> entityDbFieldMap) {
			this.entityDbFieldMap = entityDbFieldMap;
		}
	}




	@SuppressWarnings("unchecked")
	public <T> T selectOneRecord(Class<T> clazz,String sql,Object ...args){
		T clazzInstance = null;
		try {
			clazzInstance = clazz.newInstance();
			ComboFieldData fieldStrList = getFieldStrList(clazz);
			Map<String, String> entityDbFieldMap = fieldStrList.getEntityDbFieldMap();
			Map map = unique(sql, args);
			if(map==null) return null;
			mapToBean(map,clazzInstance,entityDbFieldMap);
			return  clazzInstance;
		} catch (IllegalArgumentException e) {
			e.printStackTrace();
			System.err.println("SqLiteUtils. 249 line error IllegalArgumentException" + e.getMessage());
		} catch (IllegalAccessException e) {
			e.printStackTrace();
			System.err.println("SqLiteUtils. 249 line error IllegalAccessException" + e.getMessage());
		} catch (InstantiationException e) {
			e.printStackTrace();
			System.err.println("SqLiteUtils. 248 line error InstantiationException 类实例化异常" + e.getMessage());
		}
		return clazzInstance;
	}

	public <T> List<T> selectListRecord(Class<T> clazz,String sql) {
		List<Map<String, Object>> list = list(sql);
		List<T> resultList = new ArrayList<>();
		for (Map<String, Object> map : list) {
			T clazzInstance = null;
			try {
				clazzInstance = clazz.newInstance();
				ComboFieldData fieldStrList = getFieldStrList(clazz);
				Map<String, String> entityDbFieldMap = fieldStrList.getEntityDbFieldMap();
				if (map == null) continue;
				mapToBean(map,clazzInstance,entityDbFieldMap);
				resultList.add(clazzInstance);
			} catch (IllegalArgumentException e) {
				e.printStackTrace();
			} catch (IllegalAccessException e) {
				e.printStackTrace();
			} catch (InstantiationException e) {
				e.printStackTrace();
			}
		}
		return resultList;
	}

	public void begin() throws SQLException {
		connection.setAutoCommit(false);
	}

	public void commit() throws SQLException {
		connection.commit();
	}

	public void rollback() throws SQLException {
		connection.rollback();
	}

	public String getDataSource() {
		return dataSource;
	}

	public void setDataSource(String dataSource) {
		this.dataSource = dataSource;
	}


	public String getUserTempl() {
		return userTempl;
	}


	public void setUserTempl(String userTempl) {
		this.userTempl = userTempl;
	}
	
	public String getUnit() {
		return unit;
	}

	public void setUnit(String unit) {
		this.unit = unit;
	}
}
